package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.RegionDto;
import com.hualude.roadsign.model.RegionEntity;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

/**
 * Created by xu-jiaHui on 2017/4/17.
 */
public interface RegionRepository extends CrudRepository<RegionEntity, Long> {


    /**
     * @Description: 动态加载省下拉框
     * @Author: xu-jiaHui
     * @Date: 2017/4/17 22:25
     */
    @Query(value = "select new com.hualude.roadsign.dto.RegionDto(regionId,regionName) from RegionEntity where regionType=0")
    public List<RegionDto> getProvince4Select() throws Exception;

    /**
     * @Description: 动态加载市区下拉框
     * @Author: xu-jiaHui
     * @Date: 2017/4/17 22:26
     */
    @Query(value = "select new com.hualude.roadsign.dto.RegionDto(regionId,regionName) from RegionEntity where regionFatherId=?1")
    public List<RegionDto> getCity4Select(Integer pid) throws Exception;

    /**
     * @Description: 根据省获取行政编码
     * @Author: shao-yikun
     * @Date: 2017/4/21 21:14
     */
    @Query(value = "select regionId from RegionEntity where regionType = 0 and regionName = ?1")
    public Integer getProvinceRegionId(String province) throws Exception;


    /**
     * @Description: 根据市获取行政编码
     * @Author: shao-yikun
     * @Date: 2017/4/21 21:14
     */
    @Query(value = "select regionId from RegionEntity where regionType = 1 and regionFatherId = ?1 and regionName = ?2")
    public Integer getCityRegionId(Integer regionId, String city) throws Exception;

    /**
     * @Description: 根据区县获取行政编码
     * @Author: shao-yikun
     * @Date: 2017/4/21 21:14
     */
    @Query(value = "select regionId from RegionEntity where regionType = 2 and regionFatherId = ?1 and regionName = ?2")
    public Integer getAreaRegionId(Integer regionId, String area) throws Exception;

}
